#!/bin/bash


export HIVE_HOME=/usr/bin/hive

if [ $# == 1 ]

then
   dateStr=$1
   
else
   dateStr=`date -d '-1 day' +'%Y-%m-%d'`
  

fi
yearStr=`date -d ${dateStr} +'%Y'`
month_for_quarter=`date -d ${dateStr} +%-m`
quarterStr=$((($month_for_quarter-1)/3+1))
monthStr=`date -d ${dateStr} +'%m'`
dayStr=`date -d ${dateStr} +'%d'`

echo ${yearStr}
echo ${quarterStr}
echo ${monthStr}
echo ${dayStr}

hiveSql="

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set hive.exec.orc.compression.strategy=COMPRESSION;


alter table edu_dm.visit_consult_dm drop partition(yearinfo='${yearStr}');


insert into table zx_dm.dm_web_chat_ems partition(yearinfo,quarterinfo,monthinfo,dayinfo)
select
  count(distinct sid) as visit_sid,
  count(distinct session_id) as visit_session_id,
  count(distinct ip) as visit_ip,
  count(distinct if(msg_count>=1, sid, null)) as consult_sid,
  count(distinct if(msg_count>=1, session_id, null)) as consult_session_id,
  count(distinct if(msg_count>=1, ip, null)) as consult_ip,
  hourinfo, 
  area, 
  origin_channel, 
  seo_source, 
  from_url, 
  case
    when grouping__id in (31, 63, 95, 159, 287)
    then '1'
    when grouping__id in (15, 47, 79, 143, 271)
    then '2'
    when grouping__id in (7, 39, 71, 135, 263)
    then '3'
    when grouping__id in (3, 35, 67, 131, 259)
    then '4'
    when grouping__id in (1, 33, 65, 129, 257)
    then '5'
  end
  as time_type, 
  case
    when grouping__id in (31,15,7,3,1)
    then '5'
    when grouping__id in (63,47,39,35,33)
    then '1'
    when grouping__id in (95,79,71,67,65)
    then '2'
    when grouping__id in (159,143,135,131,129)
    then '3'
    when grouping__id in (287,271,263,259,257)
    then '4'
  end
  as group_type, 
  yearinfo,
  quarterinfo, 
  monthinfo, 
  dayinfo
from zx_dwb.dwd_web_chat_ems
where yearinfo = '${yearStr}'
group by yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,area,origin_channel,seo_source,from_url
grouping sets (
    (yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo),
    (yearinfo,quarterinfo,monthinfo,dayinfo),
    (yearinfo,quarterinfo,monthinfo),
    (yearinfo,quarterinfo),
    (yearinfo),
    (area,yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo),
    (area,yearinfo,quarterinfo,monthinfo,dayinfo),
    (area,yearinfo,quarterinfo,monthinfo),
    (area,yearinfo,quarterinfo),
    (area,yearinfo),
    (origin_channel,yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo),
    (origin_channel,yearinfo,quarterinfo,monthinfo,dayinfo),
    (origin_channel,yearinfo,quarterinfo,monthinfo),
    (origin_channel,yearinfo,quarterinfo),
    (origin_channel,yearinfo),
    (seo_source,yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo),
    (seo_source,yearinfo,quarterinfo,monthinfo,dayinfo),
    (seo_source,yearinfo,quarterinfo,monthinfo),
    (seo_source,yearinfo,quarterinfo),
    (seo_source,yearinfo),
    (from_url,yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo),
    (from_url,yearinfo,quarterinfo,monthinfo,dayinfo),
    (from_url,yearinfo,quarterinfo,monthinfo),
    (from_url,yearinfo,quarterinfo),
    (from_url,yearinfo)
);
"

${HIVE_HOME} -S -e "${hiveSql}"